﻿using NPOI.HSSF.UserModel;
using NPOI.OpenXml4Net.OPC;
using NPOI.POIFS.Crypt;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dotnet.Code
{
    public class NPOIExcel : ExcelDocument
    {
        /// <summary>
        /// 读取.xlsx文件内容，控制台输出
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetName">工作簿名称</param>
        /// <param name="password">密码</param>
        //public void ReadFormExcelFile(string FilePath, string SheetName, string password)
        //{
        //    IWorkbook wk = null;

        //    try
        //    {
        //        FileStream fileStream = File.OpenRead(FilePath);
        //        POIFSFileSystem fs = new POIFSFileSystem(fileStream);

        //        EncryptionInfo info = new EncryptionInfo(fs);
        //        Decryptor decryptor = Decryptor.GetInstance(info);
        //        bool isPasswordCorrect = decryptor.VerifyPassword(password);
        //        var stream = decryptor.GetDataStream(fs);

        //        wk = new XSSFWorkbook(stream);



        //        ISheet sheet = wk.GetSheet(SheetName);
        //        IRow row = sheet.GetRow(0);

        //        for (int i = 0; i < sheet.LastRowNum; i++)
        //        {
        //            row = sheet.GetRow(i);
        //            if (row != null)
        //            {
        //                for (int j = 0; j < row.LastCellNum; j++)
        //                {
        //                    string value = row.GetCell(j).ToString();
        //                    Console.Write(value + " ");
        //                }
        //                Console.WriteLine();
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex);
        //    }
        //}

        public override DataTable ExcelToDataTable(string fileName, string pwd = "")
        {
            DecryptExcelWithPassword(fileName, pwd);

            using (FileStream fileStream = File.OpenRead(fileName))
            {
                IWorkbook wb = NPOI.SS.UserModel.WorkbookFactory.Create(fileStream);
                //EncryptionInfo info = new EncryptionInfo(fs);
                //Decryptor decryptor = Decryptor.GetInstance(info);
                //bool isPasswordCorrect = decryptor.VerifyPassword(pwd);
                //var stream = decryptor.GetDataStream(fs);
            }


            //XSSFWorkbook book = new XSSFWorkbook(fileName);
            //NPOI.XSSF.UserModel.XSSFSheet sheet = book.GetSheetAt(0) as NPOI.XSSF.UserModel.XSSFSheet;
            ////根据路径获取读取xls文件
            //HSSFWorkbook wb = new HSSFWorkbook(new FileStream(fileName, FileMode.Open));
            ////获取sheet对象
            //ISheet sheet1 = wb.GetSheet("Sheet1");
            ////获取行
            //IRow row = sheet1.GetRow(0);
            ////获取单元格
            //ICell cell = row.GetCell(0);
            //FileStream fileStream = File.OpenRead(fileName);
            //POIFSFileSystem fs = new POIFSFileSystem(fileStream);

            //EncryptionInfo info = new EncryptionInfo(fs);
            //Decryptor decryptor = Decryptor.GetInstance(info);
            //bool isPasswordCorrect = decryptor.VerifyPassword(pwd);
            //var stream = decryptor.GetDataStream(fs);

            //XSSFWorkbook wk = new XSSFWorkbook(stream);



            //ISheet sheet = wk.GetSheet(SheetName);



            return base.ExcelToDataTable(fileName, pwd);
        }

        public void DecryptExcelWithPassword(string encryptedFilePath, string password)
        {
            // 打开包含密码的Excel文件
            using (Package.Open(encryptedFilePath, FileMode.Open, FileAccess.Read))
            {
                OPCPackage pkg = OPCPackage.Open(encryptedFilePath, PackageAccess.READ);

                XSSFRelation xssfRelation = XSSFRelation.WORKBOOK;
                PackagePartName partName = PackagingUriHelper.CreatePartName(xssfRelation.DefaultFileName);
                NPOI.OpenXml4Net.OPC.PackagePart part = pkg.GetPart(partName);

                // 加载工作簿
                //using (Stream stream = part.GetStream())
                //{
                //    IWorkbook workbook = new XSSFWorkbook(stream);

                //    // 操作工作簿
                //    // ...

                //    // 关闭工作簿
                //    workbook.Close();
                //}
            }
        }
    }
}
